<?php
/**
 * 封装复杂DB
 *
 * 注意：仅支持laravel5.3版本或以上版本
 *
 * @author    xiecz <xiecz@yunys.com.cn>
 * @copyright 2016 QuanZhouYunYiShang Inc.
 */
namespace App\Services\Tool\DataBase;

use Illuminate\Support\Facades;

class DB_MYSQL_XCZ{

    public  $table_name;

    private $where = [];
    private $whereThen = [];
    private $orWhere = [];
    private $orWhereThen = [];
    private $whereBetween = [];
    private $orWhereBetween = [];
    private $like = [];
    private $orLike = [];
    private $whereIn = [];
    private $orWhereIn = [];
    private $whereNotIn = [];
    private $orWhereNotIn = [];
    private $whereColumn = [];
    private $orWhereColumn = [];
    private $skip = 0;
    private $take = 0;

    private $select = [];

    public  $orderBy_column;
    public  $orderBy_sort = 'asc';
    private $groupBy = [];
    public  $inRandomOrder = 0; //inRandomOrder方法可用于对查询结果集进行随机排序，比如，你可以用该方法获取一个随机用户

    function __construct($table_name){
        $this->table_name = $table_name;
    }

    function clean_condition(){
        $this->where = [];
        $this->orWhere = [];
        $this->whereBetween = [];
        $this->orWhereBetween = [];
        $this->like = [];
        $this->orLike = [];
        $this->whereIn = [];
        $this->orWhereIn = [];
        $this->whereNotIn = [];
        $this->orWhereNotIn = [];
        $this->whereColumn = [];
        $this->orWhereColumn = [];
        $this->skip = 0;
        $this->take = 0;

        $this->select = [];

        $this->orderBy_column;
        $this->orderBy_sort = 'asc';
        $this->groupBy = [];
        $this->inRandomOrder = 0; //inRandomOrder方法可用于对查询结果集进行随机排序，比如，你可以用该方法获取一个随机用户
    }

    /**
     * 条件函数
     */

    function where(array $value, $is_clean = 0 ,$is_or = 0){
        switch($is_clean){
            case 0 :
                if($is_or){
                    $this->orWhere[] = $value;
                }else{
                    $this->where[] = $value;
                }
                break;
            case 1:
                if($is_or){
                    $this->orWhere   = [];
                    $this->orWhere[] = $value;
                }else{
                    $this->where   = [];
                    $this->where[] = $value;
                }
                break;
        }

        return true;
    }

    function whereThen(array $value, $is_clean = 0 ,$is_or = 0){
        if(count($value) != 3) return false;
        switch($is_clean){
            case 0 :
                if($is_or){
                    $this->orWhereThen[] = $value;
                }else{
                    $this->whereThen[] = $value;
                }
                break;
            case 1:
                if($is_or){
                    $this->orWhereThen   = [];
                    $this->orWhereThen[] = $value;
                }else{
                    $this->whereThen   = [];
                    $this->whereThen[] = $value;
                }
                break;
        }

        return true;
    }

    function whereBetween(array $value, $is_clean = 0, $is_or = 0){
        if(count($value) == 2 &&
           is_string($value[0]) &&
           is_array($value[1]) &&
           count($value[1]) == 2){
            switch($is_clean){
                case 0 :
                    if($is_or)
                        $this->orWhereBetween[] = $value;
                    else
                        $this->whereBetween[] = $value;
                    break;
                case 1:
                    if($is_or){
                        $this->orWhereBetween   = [];
                        $this->orWhereBetween[] = $value;
                    }else{
                        $this->whereBetween   = [];
                        $this->whereBetween[] = $value;
                    }
                    break;
            }

            return true;
        }else{
            return false;
            Throw new \Exception('whereBetween数据格式错误');
        }
    }

    function whereIn(array $value, $is_clean = 0, $is_or = 0){
        if(count($value) == 2 &&
           is_string($value[0]) &&
           is_array($value[1])){
            switch($is_clean){
                case 0 :
                    if($is_or)
                        $this->orWhereIn[] = $value;
                    else
                        $this->whereIn[] = $value;
                    break;
                case 1:
                    if($is_or){
                        $this->orWhereIn   = [];
                        $this->orWhereIn[] = $value;
                    }else{
                        $this->whereIn   = [];
                        $this->whereIn[] = $value;
                    }
                    break;
            }

            return true;
        }else{
            return false;
            Throw new \Exception('whereIn数据格式错误');
        }
    }

    function whereNotIn(array $value, $is_clean = 0, $is_or = 0){
        if(count($value) == 2 &&
           is_string($value[0]) &&
           is_array($value[1])){
            switch($is_clean){
                case 0 :
                    if($is_or)
                        $this->orWhereNotIn[] = $value;
                    else
                        $this->whereNotIn[] = $value;
                    break;
                case 1:
                    if($is_or){
                        $this->orWhereNotIn   = [];
                        $this->orWhereNotIn[] = $value;
                    }else{
                        $this->whereNotIn   = [];
                        $this->whereNotIn[] = $value;
                    }
                    break;
            }

            return true;
        }else{
            return false;
            Throw new \Exception('whereNotIn数据格式错误');
        }
    }

    function like(string $column, $value, $is_clean = 0, $is_or = 0){
        if(!is_object($value) && !is_array($value) && $column != ''){
            switch($is_clean){
                case 0 :
                    if($is_or)
                        $this->orLike[] = [$column,$value];
                    else
                        $this->like[] = [$column,$value];
                    break;
                case 1:
                    if($is_or){
                        $this->orLike   = [];
                        $this->orLike[] = [$column,$value];
                    }else{
                        $this->like   = [];
                        $this->like[] = [$column,$value];
                    }
                    break;
            }

            return true;
        }
        return false;
        Throw new \Exception('like数据格式错误');
    }

    function whereColumn(array $value, $is_clean = 0, $is_or = 0){
        if(count($value) == 2 && is_string($value[0]) && is_string($value[1])){
            switch($is_clean){
                case 0 :
                    if($is_or)
                        $this->orWhereColumn[] = [$value[0], '=', $value[1]];
                    else
                        $this->whereColumn[] = [$value[0], '=', $value[1]];
                    break;
                case 1:
                    if($is_or){
                        $this->orWhereColumn   = [];
                        $this->orWhereColumn[] = [$value[0], '=', $value[1]];
                    }else{
                        $this->whereColumn   = [];
                        $this->whereColumn[] = [$value[0], '=', $value[1]];
                    }
                    break;
            }

            return true;
        }else if(count($value) == 3 && is_string($value[0]) && is_string($value[1]) && is_string($value[2])){
            switch($is_clean){
                case 0 :
                    if($is_or)
                        $this->orWhereColumn[] = [$value[0], $value[1], $value[2]];
                    else
                        $this->whereColumn[] = [$value[0], $value[1], $value[2]];
                    break;
                case 1:
                    if($is_or){
                        $this->orWhereColumn   = [];
                        $this->orWhereColumn[] = [$value[0], $value[1], $value[2]];
                    }else{
                        $this->whereColumn   = [];
                        $this->whereColumn[] = [$value[0], $value[1], $value[2]];
                    }
                    break;
            }

            return true;
        }
        return false;
        Throw new \Exception('whereColumn数据格式错误');
    }

    function skip(int $num){
        if($num > 0){
            $this->skip = $num;
            return true;
        }
        return false;
        Throw new \Exception('skip数据格式错误');
    }

    function take(int $num){
        if($num > 0){
            $this->take = $num;
            return true;
        }
        return false;
        Throw new \Exception('skip数据格式错误');
    }

    /**
     * 排序函数
     */

    function groupBy(string $value, $is_clean = 0){
        if($value != ''){
            switch($is_clean){
                case 0 :
                    $this->groupBy[] = $value;
                    break;
                case 1:
                    $this->groupBy   = [];
                    $this->groupBy[] = $value;
                    break;
            }
            return true;
        }else{
            return false;
            Throw new \Exception('groupBy数据格式错误');
        }
    }



    function select(string $value, $is_clean = 0){
        if($value != ''){
            switch($is_clean){
                case 0 :
                    $this->select[] = $value;
                    break;
                case 1:
                    $this->select   = [];
                    $this->select[] = $value;
                    break;
            }

            return true;
        }
        return false;
        Throw new \Exception('select数据格式错误');
    }

    /**
     * 终结函数
     * 返回数据
     */
    function first(){
        $str  = $this->use_table_str();
        $str .= $this->getConditionToStr();
        $str .= '->first();';
        $str .= 'return $result;';
        $result = eval($str);
        return $result;
    }

    function pluck($column){
        $str  = $this->use_table_str();
        $str .= $this->getConditionToStr();
        $str .= '->pluck("' . $column . '")->toArray();';
        $str .= 'return $result;';
        $result = eval($str);
        return $result;
    }

    function value($column){
        $str  = $this->use_table_str();
        $str .= $this->getConditionToStr();
        $str .= '->value("' . $column . '");';
        $str .= 'return $result;';
        $result = eval($str);
        return $result;
    }

    function get(){
        $str  = $this->use_table_str();
        $str .= $this->getConditionToStr();
        $str .= '->get()->toArray();';
        $str .= 'return $result;';
        $result = eval($str);
        return $result;
    }

    function paginate($per_page = 10,$is_toArray = 0){
        $str  = $this->use_table_str();
        $str .= $this->getConditionToStr();
        $str .= '->paginate(' . $per_page . ')';
        if($is_toArray){
            $str .= '->toArray();';
        }else{
            $str .= ';';
        }
        $str .= 'return $result;';
        $result = eval($str);
        return $result;
    }

    function insert($insert){
        $str  = $this->use_table_str();
        $str .= $this->getConditionToStr();
        $str .= '->insertGetId(' . self::array_to_str($insert) . ');';
        $str .= 'return $result;';
        $result = eval($str);
        return $result;
    }

    function update($update){
        $str  = $this->use_table_str();
        $str .= $this->getConditionToStr();
        $str .= '->update(' . self::array_to_str($update) . ');';
        $str .= 'return $result;';
        $result = eval($str);
        return $result;
    }

    function delete(){
        $str  = $this->use_table_str();
        $str .= $this->getConditionToStr();
        $str .= '->delete();';
        $str .= 'return $result;';
        $result = eval($str);
        return $result;
    }

    function count(){
        $str  = $this->use_table_str();
        $str .= $this->getConditionToStr();
        $str .= '->count();';
        $str .= 'return $result;';
        $result = eval($str);
        return $result;
    }

    /**
     * 工具
     */
    private function getConditionToStr(){
        $str = '';
        if(count($this->where) > 0){
            foreach($this->where as $one){
                $str .= '->where(' . self::array_to_str($one) . ')';
            }
        }

        if(count($this->orWhere) > 0){
            foreach($this->orWhere as $one){
                $str .= '->orWhere(' . self::array_to_str($one) . ')';
            }
        }

        if(count($this->whereThen) > 0){
            foreach($this->whereThen as $one){
                $str .= '->where("' . $one[0] . '","' . $one[1] . '","' . $one[2] . '")';
            }
        }

        if(count($this->orWhereThen) > 0){
            foreach($this->orWhereThen as $one){
                $str .= '->orWhere("' . $one[0] . '","' . $one[1] . '","' . $one[2] . '")';
            }
        }

        if(count($this->whereIn) > 0){
            foreach($this->whereIn as $one){
                $str .= '->whereIn("' . $one[0] . '",[' . self::array_unit_to_str($one[1]) . '])';
            }
        }

        if(count($this->orWhereIn) > 0){
            foreach($this->orWhereIn as $one){
                $str .= '->orWhereIn("' . $one[0] . '",[' . self::array_to_str($one[1]) . '])';
            }
        }

        if(count($this->whereBetween) > 0){
            foreach($this->whereBetween as $one){
                $str .= '->whereBetween("' . $one[0] . '",["' . $one[1][0] . '","' . $one[1][1] . '"])';
            }
        }

        if(count($this->orWhereBetween) > 0){
            foreach($this->orWhereBetween as $one){
                $str .= '->orWhereBetween("' . $one[0] . '",["' . $one[1][0] . '","' . $one[1][1] . '"])';
            }
        }

        if(count($this->whereNotIn) > 0){
            foreach($this->whereNotIn as $one){
                $str .= '->whereNotIn("' . $one[0] . '",[' . self::array_to_str($one[1]) . '])';
            }
        }

        if(count($this->orWhereNotIn) > 0){
            foreach($this->orWhereNotIn as $one){
                $str .= '->orWhereNotIn("' . $one[0] . '",[' . self::array_to_str($one[1]) . '])';
            }
        }

        if(count($this->like) > 0){
            foreach($this->like as $one){
                $str .= '->where("' . $one[0] . '", "like", "' . $one[1] . '")';
            }
        }

        if(count($this->whereColumn) > 0){
            foreach($this->whereColumn as $one){
                $str .= '->whereColumn("' . $one[0] . '", "' . $one[1] . '", "' . $one[2] . '")';
            }
        }

        if(count($this->orWhereColumn) > 0){
            foreach($this->orWhereColumn as $one){
                $str .= '->orWhereColumn("' . $one[0] . '", "' . $one[1] . '", "' . $one[2] . '")';
            }
        }

        if(count($this->orLike) > 0){
            foreach($this->orLike as $one){
                $str .= '->orWhere("' . $one[0] . '", "like", "' . $one[1] . '")';
            }
        }

        if(count($this->groupBy) > 0){
            foreach($this->groupBy as $one){
                $str .= '->groupBy("' . $one . '")';
            }
        }

        if(isset($this->orderBy_column) && $this->orderBy_column != ''){
            $str .= '->orderBy("' . $this->orderBy_column . '","' . $this->orderBy_sort . '")';
        }

        if($this->inRandomOrder){
            $str .= '->inRandomOrder()';
        }

        if(count($this->select) > 0){
            $str .= '->select(';
            foreach($this->select as $one){
                $str .= '"' . $one . '",';
            }
            $str .= ')';
        }
        $str = str_replace(';',' ',$str);
        return $str;
    }

    /**
     * 数组转字符串
     */
    private static function array_to_str(array $ar){

        $str = '[';
        foreach($ar as $k=>$v){
            $str .= '"' . $k . '"=>"' . $v . '",';
        }
        if(strstr($str,',')){
            $str = substr($str,0,strlen($str)-strlen(','));
        }
        $str .= ']';

        return $str;
    }

    /**
     * 一维数组转字符串
     */
    private static function array_unit_to_str(array $ar){
        $str = '';
        foreach($ar as $a){
            if($str != '') $str .= ',';
            $str .= '"' . $a . '"';
        }
        return $str;
    }

    /**
     * 获取use table 字符串
     */
    private function use_table_str(){
        $str  = 'use Illuminate\Support\Facades\DB;';
        $str .= '$result = DB::table("' . $this->table_name . '")' ;
        return $str;
    }
}
